<?php
/*
 * 借款
 */
namespace app\home\logic;

use think\Model;

class Borrow extends Model {
    //平台数据
    public function getSevenAccount(){
        $arr = [
            'thisMonth' => ' and addtime > ' . strtotime(date('Y-m', time())),
        'lastMonth' => ' and addtime > ' . strtotime(date('Y-m', strtotime('-1 month'))) . ' and addtime < ' . strtotime(date('Y-m', time())),

        'yesterday' => ' and addtime > ' . strtotime(date('Y-m-d', strtotime('-1 day'))) . ' and addtime < ' . strtotime(date('Y-m-d', time())),

        'today' => ' and addtime > ' . strtotime(date('Y-m-d', time())),
        'all' => ''

            ];

        $prefix = config('database.prefix');
        $where = " status=3  and borrow_status=1 and borrow_full_status=1";
        foreach ($arr as $k => $v) {
            $sql = "select sum(amount_account) as account,sum(repay_account_interest) as interest , count(1) total from `{$prefix}borrow` where ".$where.$v;
            $count = db()->query($sql);
            $count = $count[0];
            $result['account'] = isset($count['account'])?$count['account']:'0.00';
            $result['interest'] = isset($count['interest'])?$count['interest']:'0.00';
            $result['total'] = isset($count['total'])?$count['total']:'0.00';
            $res[$k] = $result;
        }
        return $res;
    }

    //投资人借出统计
    public function GetUsersRecoverCount($data = []) {
        if (!$data['user_id']) {
            return null;
        }
        $prefix = config('database.prefix');
        //统计已赚1,已经回收完的，2，没有转让成功所得的已收款，3转让成功后所得的期数的已收款
        $_where = " where ((p1.user_id='{$data['user_id']}' and p2.change_status!=1 ) or (p1.user_id='{$data['user_id']}' and p2.change_status=1 and p1.recover_period<=p3.borrow_period-p2.change_period) or (p2.change_userid='{$data['user_id']}' and p2.change_status=1 and p1.recover_period>p3.borrow_period-p2.change_period))";

        $sql = "select p1.recover_status,sum(p1.recover_account) as anum,sum(p1.recover_capital) as cnum,sum(p1.recover_interest) as inum,sum(p1.recover_interest_yes) as iynum,sum(p1.recover_account_yes) as aynum,sum(p1.recover_capital_yes) as cynum,count(1) as num,count(distinct p1.borrow_nid) as times  from  `{$prefix}borrow_recover` as p1 
        left join `{$prefix}borrow_tender` as p2 on p1.tender_id=p2.id 
        left join `{$prefix}borrow` as p3 on p1.borrow_nid=p3.borrow_nid 
         {$_where} group by p1.recover_status";
        $result = db()->query($sql);

        //借出的统计
        $_result = array();
        $_result["tender_interest_account"] = 0;
        $_result["tender_success_account"] = 0;
        $_result["tender_account"] = 0;
        $_result["recover_yes_interest"] = 0;
        $_result["recover_yes_capital"] = 0;
        $_result["recover_wait_account"] = 0;//未收总额
        foreach ($result as $key => $value) {
            if ($value["recover_status"] == 1) {
                $_result["recover_yes_account"] = $value["aynum"];//已收总额
                $_result["recover_yes_account_all"] = $value["anum"];//已收总额
                $_result["recover_yes_capital"] = $value["cynum"];//已收本金总额
                $_result["recover_yes_capital_all"] = $value["cnum"];//已收本金总额
                $_result["recover_yes_interest"] = $value["iynum"];//已收利息
                $_result["recover_yes_num"] = $value["num"];//已收期数
                $_result["recover_yes_times"] = $value["times"];//已收笔数
            } elseif ($value["recover_status"] == 0) {
                $_result["recover_wait_account"] = $value["anum"];//未收总额
                $_result["recover_wait_capital"] = $value["cnum"];//未收本金总额
                $_result["recover_wait_interest"] = $value["inum"];//未收利息
                $_result["recover_wait_num"] = $value["num"];//未收期数
                $_result["recover_wait_times"] = $value["times"];//未收笔数
            }
            $_result["tender_success_account"] += $value["cnum"];//成功借出总额
            $_result["tender_interest_account"] += $value["inum"];//总利息
            $_result["tender_account"] += $value["anum"];//借出总额
        }

        //最近待收
        $sql = "select p1.* from `{$prefix}borrow_recover` as p1 
        left join `{$prefix}borrow_tender` as p2 on p1.tender_id=p2.id 
        left join `{$prefix}borrow` as p3 on p1.borrow_nid=p3.borrow_nid 
       {$_where} and p1.recover_status=0 order by p1.recover_time asc";
        $excute = db()->query($sql);
        if ($excute) {
            $result = $excute[0];
            $_result["recover_wait_now_account"] = $result["recover_account"];//最近待收总额
            $_result["recover_wait_now_interest"] = $result["recover_interest"];//最近待收利息
            $_result["recover_wait_now_time"] = $result["recover_time"];//最近待收时间
        }

        //总借出统计
        $sql = "select status,sum(account) as anum,count(1) as num  from `{$prefix}borrow_tender` where user_id='{$data['user_id']}' and status=0";
        $excute = db()->query($sql);
        $result = $excute[0];
        $_result["tender_now_account"] = $result["anum"];//投资中的总额
        $_result["tender_now_num"] = $result["num"];//投资中成功笔数


        //投资奖励
        $sql = "select sum(tender_award_fee) as tnum,count(1) as num  from `{$prefix}borrow_tender`  where user_id='{$data['user_id']}'  and tender_award_fee>0 ";
        $excute = db()->query($sql);
        $result = $excute[0];
        $_result["tender_award_fee"] = $result["tnum"];//奖励总额
        $_result["tender_award_num"] = $result["num"];//奖励笔数

        //逾期
        $sql = "select p1.recover_status,sum(p1.recover_capital) as anum,count(1) as num  from `{$prefix}borrow_recover` as p1 
        left join `{$prefix}borrow_tender` as p2 on p1.tender_id=p2.id 
        left join `{$prefix}borrow` as p3 on p1.borrow_nid=p3.borrow_nid 
         {$_where} and p1.recover_time<=" . (time() - 120 * 60 * 24) . " group by p1.recover_status";
        $result = db()->query($sql);
        $_result["tender_late_account"] = 0;
        $_result["recover_late_no_account"] = 0;
        $_result["tender_late_num"] = 0;
        if ($result) {
            foreach ($result as $key => $value) {
                if ($value["recover_status"] == 1) {
                    $_result["recover_late_yes_account"] = $value["anum"];//逾期已还总额
                    $_result["recover_late_yes_num"] = $value["num"];//逾期已还笔数
                } elseif ($value["recover_status"] == 0) {
                    $_result["recover_late_no_account"] = $value["anum"];//逾期未还总额
                    $_result["recover_late_no_num"] = $value["num"];//逾期未还笔数
                }
                $_result["tender_late_account"] += $value["anum"];//逾期未还总额
                $_result["tender_late_num"] += $value["num"];//逾期未还笔数
            }
        }

        //网站垫付
        $sql = "select sum(p1.recover_account_yes) as anum,count(1) as num  from `{$prefix}borrow_recover` as p1 
        left join `{$prefix}borrow_tender` as p2 on p1.tender_id=p2.id 
        left join `{$prefix}borrow` as p3 on p1.borrow_nid=p3.borrow_nid 
        {$_where} and p1.recover_web=1 ";
        $excute = db()->query($sql);
        $result = $excute[0];

        $_result["recover_web_account"] = $result["anum"];//网站垫付总额
        $_result["recover_web_num"] = $result["num"];//网站垫付笔数


        //损失利息总额
        $sql = "select  sum(recover_advance_fee) as fnum,sum(p1.recover_interest-p1.recover_interest_yes) as anum,count(1) as num  from `{$prefix}borrow_recover`   as p1 
        left join `{$prefix}borrow_tender` as p2 on p1.tender_id=p2.id 
        left join `{$prefix}borrow` as p3 on p1.borrow_nid=p3.borrow_nid 
        {$_where}  and p1.recover_status=1 and p1.recover_type='advance' ";
        $excute = db()->query($sql);
        $result = $excute[0];
        $_result["recover_loss_account"] = $result["anum"];//损失利息
        $_result["recover_loss_num"] = $result["num"];//损失利息笔数
        $_result["tender_advance_account"] = $result["fnum"];//提前还款费用
        $_result["tender_advance_num"] = $result["num"];//提前还款笔数

        //已赚罚息=逾期罚金=网站未垫付前借款人还款得到的逾期罚金费用总计
        $sql = "select sum(p1.recover_late_fee) as anum,count(1) as num  from `{$prefix}borrow_recover` as p1 
        left join `{$prefix}borrow_tender` as p2 on p1.tender_id=p2.id 
        left join `{$prefix}borrow` as p3 on p1.borrow_nid=p3.borrow_nid 
       {$_where}   and p1.recover_status=1 and p1.recover_type='late' ";
        $excute = db()->query($sql);
        $result = $excute[0];
        $_result["recover_fee_account"] = $result["anum"];//罚息总额
        $_result["recover_fee_num"] = $result["num"];//罚息笔数
        $_result["tender_recover_scale"] = 0;
        $_result["tender_false_scale"] = 0;
        if ($_result["tender_success_account"] > 0) {
            //平均收益率
            $totalyz = $_result["recover_yes_interest"] + $_result["tender_award_fee"];
            if ($_result["recover_yes_capital"] != 0) {
                $_result["tender_recover_scale"] = round($totalyz * 100 / $_result["recover_yes_capital"], 2);
            }

            //坏账率
            $_result["tender_false_scale"] = round($_result["recover_late_no_account"] / $_result["tender_success_account"], 2) * 100;
        }
        return $_result;
    }

    public function getList($data = []) {

        $_sql = "where 1=1 ";
        //判断用户id
        if (isset($data['user_id'])) {
            $_sql .= " and p1.user_id = {$data['user_id']}";
        }

        //搜到用户名
        if (isset($data['username'])) {
            $_sql .= " and p2.username like \"%{$data['username']}%\"";
        }

        //搜到借款人
        if (isset($data['vouch_users'])) {
            $_sql .= " and p1.vouch_users like \"%{$data['vouch_users']}%\"";
        }

        if (isset($data['tiro_status'])) {
            if ($data['tiro_status'] == '1') {
                $_sql .= " and p1.tiro_status = 1 ";
            } elseif ($data['tiro_status'] == '0') {
                $_sql .= " and (p1.tiro_status is null or p1.tiro_status=0) ";
            }
        }
        //搜索借款名称
        if (isset($data['borrow_name'])) {
            //$data['borrow_name']= addslashes(urldecode($data['borrow_name']));
            //$_sql .= " and p1.`name` like '%{$data['borrow_name']}%'";
            //$data['borrow_name']=Fliter::fliter_deayou(urldecode($data['borrow_name']));
            $_sql .= " and p1.`name` like \"%{$data['borrow_name']}%\"";
        }
        //搜索借款IDstatus_nid
        if (isset($data['borrow_nid'])) {
            $_sql .= " and p1.`borrow_nid` like '%{$data['borrow_nid']}%'";
        }

        //搜索利率
        if (isset($data['borrow_interestrate'])) {
            if ($data['borrow_interestrate'] == 1) {
                $_sql .= " and p1.`borrow_apr` > 0 and p1.`borrow_apr` <= 12";
            }
            if ($data['borrow_interestrate'] == 2) {
                $_sql .= " and p1.`borrow_apr` > 12 and p1.`borrow_apr` <= 15";
            }
            if ($data['borrow_interestrate'] == 3) {
                $_sql .= " and p1.`borrow_apr` > 15 and p1.`borrow_apr` <= 18";
            }
            if ($data['borrow_interestrate'] == 4) {
                $_sql .= " and p1.`borrow_apr` > 18";
            }
        }
        //搜索期限
        if (isset($data['spread_month'])) {
            if ($data['spread_month'] == 1) {
                $_sql .= " and ((p1.`borrow_period` <= 1 and p1.`borrow_style` <> 'endday') or  p1.`borrow_style` = 'endday')";
            }
            if ($data['spread_month'] == 2) {
                $_sql .= " and p1.`borrow_period` > 1 and p1.`borrow_period` <= 3 and p1.`borrow_style` <> 'endday'";
            }
            if ($data['spread_month'] == 3) {
                $_sql .= " and p1.`borrow_period` > 3 and p1.`borrow_period` <= 6 and p1.`borrow_style` <> 'endday'";
            }
            if ($data['spread_month'] == 4) {
                $_sql .= " and p1.`borrow_period` > 6 and p1.`borrow_period` <= 9 and p1.`borrow_style` <> 'endday'";
            }
            if ($data['spread_month'] == 5) {
                $_sql .= " and p1.`borrow_period` > 9 and p1.`borrow_period` <= 12 and p1.`borrow_style` <> 'endday'";
            }
            if ($data['spread_month'] == 6) {
                $_sql .= " and p1.`borrow_period` > 12 and p1.`borrow_style` <> 'endday' ";
            }
        }

        //搜索借款类型
        if (isset($data['borrow_type'])) {
            $_sql .= " and p1.borrow_type = '{$data['borrow_type']}'";
        }

        //初审的借款
        if (isset($data['query_type']) && $data['query_type'] == "first" && isset($data['status_nid']) && $data['status_nid'] == "") {
            $data['status_nid'] = "first";
        }
        //满标的借款
        if (isset($data['query_type']) && $data['query_type'] == "full" && (!isset($data['status_nid']) || $data['status_nid'] == "")) {
            $data['status_nid'] = "full";
        }

        //正在招标中的借款
        if (isset($data['loaning']) && $data['loaning'] == 1) {
            $_sql .= " and (p1.status=1 or (p1.status=3 and p1.borrow_type='roam'))  and p1.borrow_end_time>" . time() . "  and p1.borrow_account_scale!=100 ";
        }


        //搜索借款状态
        if (isset($data['status_nid'])) {
            $status_nid = $data['status_nid'];
            //初审中
            if ($status_nid == "first") {
                $_sql .= " and p1.status=0 and p1.borrow_status=0";
            } //借款中
            elseif ($status_nid == "loan") {
                $_sql .= " and p1.status!=6 and p1.borrow_status=1  and ((p1.borrow_type!='roam' and p1.status=1 ) or (p1.borrow_type='roam' and p1.account!=p1.borrow_account_yes)) and p1.borrow_end_time >" . time() . " and p1.borrow_account_wait > 0 ";
            } //初审失败
            elseif ($status_nid == "false") {
                $_sql .= " and p1.status=2 ";
            } //已过期
            elseif ($status_nid == "late") {
                $_sql .= " and p1.status=1 and p1.borrow_status=1 and p1.borrow_end_time <" . time() . " and p1.borrow_account_wait>0 ";
            } //逾期借款
            elseif ($status_nid == "lates") {
                //$_sql .= " and p1.status=3 and p1.borrow_full_status=1 and p1.repay_next_time >".time()."  ";
            } //满标
            elseif ($status_nid == "full") {
                $_sql .= " and p1.status=1 and p1.borrow_status=1 and ( p1.account=p1.borrow_account_yes or p4.part_status=1) ";
            } //满标失败
            elseif ($status_nid == "full_false") {
                $_sql .= " and p1.status = 4 ";
            } //还款中
            elseif ($status_nid == "repay") {
                $_sql .= " and p1.status=3  and p1.borrow_status=1 and p1.borrow_full_status=1 and p1.borrow_account_yes>p1.repay_account_yes and p1.repay_account_wait>0";
            } //已还完
            elseif ($status_nid == "repay_yes") {
                $_sql .= " and p1.status=3  and p1.borrow_status=1 and p1.borrow_full_status=1 and p1.repay_advance_status=0 and p1.repay_account_wait=0 ";
            } //已还完
            elseif ($status_nid == "repay_over") {
                $_sql .= " and p1.status=3  and p1.borrow_status=1 and p1.borrow_full_status=1 and repay_full_status=1 and ((p1.borrow_type='roam' and p1.tender_times=p1.repay_times) or p1.borrow_type!='roam')";
            } //已还完
            elseif ($status_nid == "repay_advance") {
                $_sql .= " and p1.status=3  and p1.borrow_status=1 and p1.borrow_full_status=1 and p1.repay_advance_status=1 ";
            } //提前还完
            elseif ($status_nid == "repay_advance") {
                $_sql .= " and p1.status=3  and p1.borrow_status=1 and p1.borrow_full_status=1 and p1.repay_full_status=1 and repay_advance_status=1 ";
            } //流标
            elseif ($status_nid == "over") {
                $_sql .= " and p1.status=6 ";
            } //流标
            elseif ($status_nid == "cancel") {
                $_sql .= " and p1.status=5 ";
            } //成功的借款
            elseif ($status_nid == "success") {
                $_sql .= " and p1.status=3 ";
            } //正在招标的借款
            elseif ($status_nid == "invest") {
                $_sql .= " and ((p1.status=1 and p1.borrow_end_time> " . time() . ") or p1.status=3)";
                //$data['order'] = "all";
            } //正在招标的借款
            elseif ($status_nid == "invest_auto") {
                $_sql .= " and (p1.status=1 and p1.borrow_end_time> " . time() . " and p1.borrow_account_wait > 0)";
                //$data['order'] = "all";
            }
            //20130402 修改  bincs   涉及流转标状态查询无效新增
            //流转中
            elseif ($status_nid == "roam_now") {
                $_sql .= " and (p1.status = 3 or p1.status=1)  and p8.portion_wait >0   ";
                //$data['order'] = "all";
            } //已回购
            elseif ($status_nid == "roam_yes") {
                $_sql .= " and p1.status = 3  and p8.portion_wait = 0 and p8.recover_wait = 0 ";
                //$data['order'] = "all";
            } //待回购
            elseif ($status_nid == "roam_no") {
                $_sql .= " and p1.status = 3   and p8.portion_wait = 0 and p8.recover_wait >0 ";
                //$data['order'] = "all";
            } //满标或还款中
            elseif ($status_nid == "full_or_repay") {
                $_sql .= " and (p1.status=1 and p1.borrow_status=1 and (p1.account=p1.borrow_account_yes or p4.part_status=1) or (p1.status=3  and p1.borrow_status=1 and p1.borrow_full_status=1 and p1.borrow_account_yes>p1.repay_account_yes and p1.repay_account_wait>0))  ";
            }
        }
        //判断添加时间结束
        if (isset($data['keywords'])) {
            $_sql .= " and p1.name like \"%{$data['keywords']}%\"";
        }

        //判断借款状态
        if (isset($data['status'])) {
            if ($data['status'] == -1) {
                $_sql .= " and p1.status = 1 and p1.borrow_valid_time*60*60*24 + p1.verify_time <" . time();
            } else {
                $_sql .= " and p1.status in ({$data['status']})";
            }
        }

        //判断是否逾期
        if (isset($data['late_display']) && $data['late_display'] == 1) {
            $_sql .= " and ((p1.status=1 and p1.verify_time >" . time() . " - p1.borrow_valid_time*60*60*24 ) or (p1.status=3 and p1.repay_account_wait>0))";
        }

        //判断是否担保借款
        if (isset($data['vouch_status'])) {
            $_sql .= " and p1.vouch_status in ({$data['vouch_status']})";
        }


        //判断是体验标
        if (isset($data['tiyan_status'])) {
            $_sql .= " and p1.tiyan_status in ({$data['tiyan_status']})";
        }

        //借款期数
        if (isset($data['borrow_period'])) {
            $data['borrow_period'] = num_check($data['borrow_period']);
            $_sql .= " and p1.borrow_period = {$data['borrow_period']}";
        }

        //借款类别
        if (isset($data['flag'])) {
            $_sql .= " and p1.flag = {$data['flag']}";
        }

        //圈子借款
        if (isset($data['group_id'])) {
            if ($data['group_id'] != "all") {
                $_sql .= " and p1.group_id = {$data['group_id']}";
            } else {
                $_sql .= "  and p1.group_id in (select group_id from `{group_member}` where user_id='{$data['my_userid']}')";
            }
        }

        //借款用户类型
        if (isset($data['borrow_usertype'])) {
            $_sql .= " and p1.borrow_usertype = '{$data['borrow_usertype']}'";
        }

        //是否奖励
        if (isset($data['award_status'])) {
            if ($data['award_status'] == 1) {
                $_sql .= " and p1.award_status >0";
            } else {
                $_sql .= " and p1.award_status = 0";
            }
        }
        //借款完成率
        if (isset($data['borrow_account_scale'])) {
            $_sql .= " and p1.borrow_account_scale = '{$data['borrow_account_scale']}'";
        }
        //借款
        if (isset($data['borrow_style'])) {
            $_sql .= " and p1.borrow_style in ({$data['borrow_style']})";
        }
        if (isset($data['omit_type'])) {
            $_sql .= " and p1.borrow_type != '{$data['omit_type']}'";
        }
        if (isset($data['account_status'])) {
            if ($data['account_status'] == 2) {
                $_sql .= " and p1.account <= 100000";
            } elseif ($data['account_status'] == 3) {
                $_sql .= " and p1.account > 100000 and p1.account <= 300000";
            } elseif ($data['account_status'] == 4) {
                $_sql .= " and p1.account > 300000 and p1.account <= 600000";
            } elseif ($data['account_status'] == 5) {
                $_sql .= " and p1.account > 600000 and p1.account <= 900000";
            } elseif ($data['account_status'] == 1) {
                $_sql .= " and p1.account > 900000";
            }
        }

        //排序
        $_order = " order by p1.`status` asc,p1.addtime desc";

        if (isset($data['status']) && $data['status'] == 1) {
            $_order = " order by p1.`order` desc,p1.addtime desc ";
        }
        if (isset($data['publish'])) {
            $_order = " order by p1.`order` desc,p1.addtime desc ";
        }
        if (isset($data['order'])) {
            $order = $data['order'];
            $type = $data['query_type'];
            if ($order == "account_up") {
                $_order = " order by p1.`account` desc ";
            } else if ($order == "account_down") {
                $_order = " order by p1.`account` asc";
            }
            if ($order == "apr_up") {
                $_order = " order by p1.`borrow_apr` desc,p1.id desc ";
            } else if ($order == "apr_down") {
                $_order = " order by p1.`borrow_apr` asc,p1.id desc ";
            }
            if ($order == "scale_up") {
                $_order = " order by p1.`borrow_account_scale` desc,p1.id desc ";

            } else if ($order == "scale_down") {
                $_order = " order by p1.`borrow_account_scale` asc,p1.id desc ";
            }
            if ($order == "period_up") {
                $_order = " order by p1.`borrow_period` desc,p1.id desc ";

            } else if ($order == "period_down") {
                $_order = " order by p1.`borrow_period` asc,p1.id desc ";
            }
            if ($order == "flag") {
                $_order = " order by p1.vouch_status desc,p1.`flag` desc,p1.id desc ";
            }
            if ($order == "index") {
                $_order = " order by p1.`flag` desc,p1.id desc ";
            }
            if ($order == "all") {
                $_order = " order by p1.borrow_account_wait desc,p1.`status` asc,p1.addtime desc";
            }
            if ($order == "indexs") {
                /* $_order = " order by p1.`status` asc,p1.id desc "; */
                $_order = " order by FIELD(p1.`status`, 1,3,0,2,4,5),p1.borrow_account_scale asc ,p1.`repay_full_status` asc,FIELD(p1.`borrow_type`, 'roam','second','credit','day','worth','pawn','vouch'),p1.addtime desc ";
            }
        } elseif (isset($data['query_type']) && ($data['query_type'] == "invest" || $data['query_type'] == "success")) {
            //1,正在投资。2，流转标。3，复审
            //$_order = " order by FIELD(p1.`status`,1,3,0,2,4,5),p1.borrow_account_scale asc ,p1.`repay_full_status` asc,FIELD(p1.`borrow_type`, 'roam','second','credit','day','worth','pawn','vouch'),p1.addtime desc ";
            //$_order = " p1.addtime desc ";
        }
        if (isset($data['field'])) {
            $_select = $data['field'];
        } else {
            $_select = " p1.*,p2.username,p8.portion_wait,p8.recover_wait,p3.credits,p3.credit,p4.name as type_name,p4.title as type_title,p5.name as style_name,p5.title as style_title,p6.live_city as city";
        }
        $prefix = config('database.prefix');
        $sql = "select SELECT from `{$prefix}borrow` as p1 
				 left join {$prefix}borrow_type as p4 on p1.borrow_type=p4.nid
				 left join {$prefix}borrow_style as p5 on p1.borrow_style=p5.nid
				 left join {$prefix}users as p2 on p1.user_id=p2.user_id
				 left join {$prefix}rating_contact as p6 on p1.user_id=p6.user_id
				 left join {$prefix}credit as p3 on p1.user_id=p3.user_id
                 left join {$prefix}borrow_roam as p8 on p1.borrow_nid=p8.borrow_nid
				";
        if (isset($data["group_status"]) && $data["group_status"] == 1) {
            $sql .= "left join `{group}` as p7 on p1.group_id=p7.id ";
            $_sql .= " and p1.group_id>0";
            //借款
            if (isset($data['group_name'])) {
                $_sql .= " and p7.`name`like '%{$data['group_name']}%'";
            }
            $_select .= ",p7.`name` as group_name";
        }
        $sql .= " SQL ORDER LIMIT ";

        //分页返回结果
        $data['page'] = isset($data['page']) ?: 1;
        $data['epage'] = isset($data['epage']) ?: 10;

        if (isset($data['limit'])) {
            $_limit = " limit " . $data['limit'];
        } else {
            $_limit = " limit " . ($data['epage'] * ($data['page'] - 1)) . ", {$data['epage']}";
        }

        $sql = str_replace(array('SELECT', 'SQL', 'ORDER', 'LIMIT'), array($_select, $_sql, $_order, $_limit), $sql);
        $list = db()->query($sql);
        foreach ($list as $key => $value) {
            //借款是否到期
            $borrow_end_status = 0;
            if ($value['status'] == 1 && $value['borrow_end_time'] < time()) {
                $borrow_end_status = 1;
            }
            $list[$key]["borrow_end_status"] = $borrow_end_status;
            $period_name = "个月";
            if ($value["borrow_type"] == "day") {
                $period_name = "天";

                $i = $value['borrow_period'] % 30;
                $day = ($value['borrow_period'] - $i) / 30;
                $list[$key]['day'] = $day;
            }
            $list[$key]["borrow_apr_1"] = $value["borrow_apr"];

            $list[$key]["borrow_period_name"] = $value["borrow_period"] . $period_name;

            $list[$key]['borrow_apr1'] = number_format($value['borrow_apr'] - $value['borrow_apr_extra'], 1);

            $borrow_status_nid = $this->GetBorrowStatusNid(array("status" => $value['status'], "account" => $value['account'], "borrow_type" => $value['borrow_type'], "borrow_end_status" => $borrow_end_status, "borrow_account_wait" => $value["borrow_account_wait"], "repay_account_wait" => $value["repay_account_wait"], "repay_full_status" => $value["repay_full_status"], "repay_advance_status" => $value["repay_advance_status"]));
            $list[$key]["status_name"] = model('linkages')->getName([
                    'nid' => 'borrow_status',
                    'type' => $borrow_status_nid
            ]);
        }
        return $list;
    }

    //新手标
    public function getNewList() {
        $borrow = $this->getList([
                'tiro_status' => 1, 'status_nid' => 'invest'
        ]);

        $result = [
                'borrow_apr' => $borrow[0]['borrow_apr'],
                'borrow_apr1' => $borrow[0]['borrow_apr1'],
                'borrow_apr_extra' => $borrow[0]['borrow_apr_extra'],
                'tender_account_min' => $borrow[0]['tender_account_min'],
                'begin_time' => $borrow[0]['begin_time'],
                'borrow_account_wait' => $borrow[0]['borrow_account_wait'],
                'borrow_period_name' => $borrow[0]['borrow_period_name'],
                'borrow_nid' => $borrow[0]['borrow_nid'],
                'id' => $borrow[0]['id'],
                'name' => $borrow[0]['name'],
                'status_name' => $borrow[0]['status_name'],
        ];
        return $result;
    }

    //正常标
    public function getBorrowList() {
        $borrow = $this->getList([
                'tiro_status' => 0, 'status_nid' => 'invest'
        ]);

        foreach ($borrow as $key => $val) {
            $result [] = [
                    'borrow_apr' => $val['borrow_apr'],
                    'borrow_apr1' => $val['borrow_apr1'],
                    'borrow_apr_extra' => $val['borrow_apr_extra'],
                    'tender_account_min' => $val['tender_account_min'],
                    'begin_time' => $val['begin_time'],
                    'borrow_account_wait' => $val['borrow_account_wait'],
                    'borrow_period_name' => $val['borrow_period_name'],
                    'borrow_nid' => $val['borrow_nid'],
                    'id' => $val['id'],
                    'name' => $val['name'],
                    'status_name' => $val['status_name'],
            ];
        }

        return $result;
    }

    function GetBorrowCredit($data = []) {
        if ($data['user_id'] == "") return false;
        $prefix = config('database.prefix');
        $_result = array();
        $sql = "select sum(credit) as num from `{$prefix}attestations` where user_id='{$data['user_id']}' and status=1";
        $attcredit = db()->query($sql);
        $sql = "select sum(credit) as tongji from `{$prefix}credit_log` where user_id='{$data['user_id']}'";
        $credit_tongji = db()->query($sql);

        $sql = "select sum(credit) as creditnum from `{$prefix}credit_log` where user_id='{$data['user_id']}' and code='borrow'";
        $credit_log = db()->query($sql);
        $sql = "select sum(credit) as creditnum from `{$prefix}credit_log` where user_id='{$data['user_id']}' and code='approve'";
        $approve = db()->query($sql);
        $sql = "select sum(credit) as creditnum from `{$prefix}credit_log` where user_id='{$data['user_id']}' and code='credit'";
        $present = db()->query($sql);
        $sql = "select sum(credit) as creditnum from `{$prefix}credit_log` where user_id='{$data['user_id']}' and code='sign'";
        $sign = db()->query($sql);

        $_result[1] = empty($attcredit[0]['num']) ? 0 : $attcredit[0]['num'];
        $_result[2] = empty($credit_log[0]['creditnum']) ? 0 : $credit_log[0]['creditnum'];
        $_result[3] = empty($approve[0]['creditnum']) ? 0 : $approve[0]['creditnum'];
        $_result[4] = empty($present[0]['creditnum']) ? 0 : $present[0]['creditnum'];
        $_result[5] = empty($sign[0]['creditnum']) ? 0 : $sign[0]['creditnum'];

        $credit_total = $_result[1] + $credit_tongji[0]['tongji'];
        $_credit['credit'] = $credit_total;
        $_credit['class'] = "borrow";
        $credit = model('credit', 'logic')->GetUserCreditRank($_credit);
        $result = array("credit_total" => $credit_total, "borrow_credit" => $_result[2] + $_result[3], "tender_credit" => $credit_tongji[0]['tongji'] - $_result[2] - $_result[3] - $_result[4], "approve_credit" => $_result[3], "present_credit" => $_result[4], "att_credit" => $_result[1], "credit" => $credit, "credit_tongji" => $credit_tongji[0]['tongji'], "credit_sign" => $_result[5]);
        //$result = array("approve_credit"=>$_result[1]+$credit_tongji['tongji'],"borrow_credit"=>$_result[2],"approve"=>$_result[3]);
        return $result;
    }

    //获取标的详情
    public function getDetail($data = []) {
        $_sql = "where 1=1 ";

        if (isset($data['hits'])) {
            db('borrow')->where(['borrow_nid' => $data['borrow_nid']])->setInc('hits');
        }

        if (isset($data['user_id'])) {
            $_sql .= " and  p1.user_id = '{$data['user_id']}' ";
        }
        if (isset($data['id'])) {
            $_sql .= " and  p1.id = '{$data['id']}' ";
        }
        if (IsExiest($data['borrow_nid']) != "") {
            $_sql .= " and  p1.borrow_nid = '{$data['borrow_nid']}' ";
        }
        $prefix = config('database.prefix');
        $sql = "select  p1.*,p2.username,p3.credits,p3.credit,p4.name as type_name,p4.title as type_title,p4.part_status as type_part_status,p4.system_borrow_full_status,p4.system_borrow_repay_status,p4.system_web_repay_status,p5.name as style_name,p5.title as style_title,p6.realname  from `{$prefix}borrow` as p1 
				 left join {$prefix}borrow_type as p4 on p1.borrow_type=p4.nid
				 left join {$prefix}borrow_style as p5 on p1.borrow_style=p5.nid
				 left join {$prefix}users as p2 on p1.user_id=p2.user_id
				 left join {$prefix}credit as p3 on p1.user_id=p3.user_id
                 left join {$prefix}users_info as p6 on p1.user_id=p6.user_id
				  $_sql
				";
        $ex = db()->query($sql);
        if ($ex == false) return false;
        $result = $ex[0];
        $result['borrow_end_time'] = $result['borrow_end_time'] ?: 0;
        $borrow_end_status = 0;
        if ($result['status'] == 1 && $result['borrow_end_time'] < time()) {
            $borrow_end_status = 1;
        }
        $result["borrow_end_status"] = $borrow_end_status;
        //借款的属性
        if ($result['flag'] != "") {
            $_flag = explode(",", $result['flag']);
            $_flag_result = [];
            foreach ($_flag as $_k => $_v) {
                $result["_flag"][] = $_flag_result[$_v];
                $flag_name[] = $_flag_result[$_v]['name'];
            }
            $result["flag_name"] = join(",", $flag_name);
        }
        $period_name = "个月";
        if ($result["borrow_type"] == "day") {
            $period_name = "天";
        }
        $result["borrow_period_name"] = $result["borrow_period"] . $period_name;
        //借款状态id的属性
        $result["borrow_status_nid"] = $this->GetBorrowStatusNid(array("status" => $result['status'], "account" => $result['account'], "borrow_type" => $result['borrow_type'], "borrow_end_status" => $result["borrow_end_status"], "borrow_account_wait" => $result["borrow_account_wait"], "repay_account_wait" => $result["repay_account_wait"], "repay_full_status" => $result["repay_full_status"], "repay_advance_status" => $result["repay_advance_status"]));
        $result["status_name"] = model('linkages')->getName([
               'nid' => 'borrow_status',
                'type' => $result["borrow_status_nid"]
        ]);

        //未还的期数 ahui 03-19
        $_result = db('borrow_repay')->where(['repay_status' => 0, 'borrow_nid' => $data['borrow_nid']])->count();
        $result["repay_wait_times"] = $_result ?: 0;

        //收益
        //$result["repay_account"] = getIncome($result['borrow_type'],$result["account"],$result["borrow_period"],$result["borrow_apr"]);

        $_equal["account"] = $result['account'];
        $_equal["period"] = $result["borrow_period"];
        $_equal["style"] = $result["borrow_style"];
        $_equal["apr"] = $result["borrow_apr"];
        $_equal["borrow_nid"] = $result['borrow_nid'];
        $_equal["type"] = "all";
        $equal_result = model('borrow_calculate', 'logic')->GetType($_equal);
        $result["repay_account"] = $equal_result['interest_total'];


        $result["borrow_apr_1"] = $result["borrow_apr"];
        $result['borrow_other_time'] = $result['borrow_end_time'] - time();
        $result['repay_account_interest_lost'] = $result['repay_account_interest'] - $result['repay_account_interest_yes'];
        $result['borrow_contents'] = htmlspecialchars_decode($result['borrow_contents']);

        $result['borrow_account_scale2'] = $result['borrow_account_scale'] - 16;
        $result['borrow_apr1'] = number_format($result['borrow_apr']-$result['borrow_apr_extra'],1);
        $result['end_time'] = $result['borrow_type']=="day"?strtotime(date("Y-m-d",$result['reverify_time'])." +".intval($result['borrow_period'])." day"):strtotime(date("Y-m-d",$result['reverify_time'])." +".intval($result['borrow_period'])." month");

        //0初审中1初审通过2初审失败
        $status = $result['status'];
        if ($status == 3) {
            if ($result['repay_account_wait'] == 0) {
                $result['status'] = 31;//已还款
            } else {
                $result['status'] = 32;//还款中
            }
        }
        if ($result['status'] == 1 && $result['borrow_end_time'] < time()) {
            $result['status'] = 11;//已过期
        }

        return $result;

    }

    /**
     * 获取借款标所属的状态
     *
     * @param Array $data = status,account,borrow_end_status,repay_account_wait,borrow_account_wait
     * @return Array
     */
    public function GetBorrowStatusNid($data = []) {
        //如果是0表示正在初审
        if ($data['status'] == 0) {
            $borrow_status_nid = "first";
        } elseif ($data['status'] == 6) {
            $borrow_status_nid = "over";
        } elseif ($data['status'] == 2) {
            $borrow_status_nid = "false";
        } elseif ($data['status'] == 3) {
            if ($data["borrow_type"] == "roam") {
                if ($data['borrow_account_wait'] > 0 and $data['portion_wait'] > 0) {
                    $borrow_status_nid = "roam_now";
                } elseif ($data['recover_wait'] > 0) {
                    $borrow_status_nid = "roam_no";
                } else {
                    $borrow_status_nid = "roam_yes";
                }
            } else {
                if ($data['repay_advance_status'] == 1) {
                    $borrow_status_nid = "repay_advance";
                } elseif ($data['repay_account_wait'] == 0) {
                    $borrow_status_nid = "repay_yes";
                } else {
                    $borrow_status_nid = "repay";
                }
            }
        } elseif ($data['status'] == 4) {
            $borrow_status_nid = "full_false";
        } elseif ($data['status'] == 5) {
            $borrow_status_nid = "cancel";
        } elseif ($data['status'] == 1) {
            if ($data["borrow_type"] == "roam") {
                $borrow_status_nid = "roam_now";
            } elseif ($data['borrow_end_status'] == 1 && $data['borrow_account_wait'] > 0) {
                $borrow_status_nid = "late";
            } elseif ($data['borrow_account_wait'] == 0) {
                $borrow_status_nid = "full";
            } else {
                $borrow_status_nid = "loan";
            }

        }
        return $borrow_status_nid;
    }

    /**
     * 查看借款标
     *
     * @param Array $data
     * @return Array
     */
    public function GetOne($data = []) {
        $prefix = config('database.prefix');
        $_sql = "where 1=1 ";
        if (isset($data['user_id'])) {
            $_sql .= " and  p1.user_id = '{$data['user_id']}' ";
        }
        if (isset($data['id'])) {
            $_sql .= " and  p1.id = '{$data['id']}' ";
        }
        if (isset($data['borrow_nid'])) {
            $_sql .= " and  p1.borrow_nid = '{$data['borrow_nid']}' ";
        }
        $sql = "select p1.* ,p2.username,p3.username as verify_username,p4.name as style_name from `{$prefix}borrow` as p1 
				  left join `{$prefix}users` as p2 on p1.user_id=p2.user_id 
				  left join `{$prefix}users` as p3 on p1.verify_userid = p3.user_id 
				  left join `{$prefix}borrow_style` as p4 on p1.borrow_style = p4.nid 
				  $_sql
				";
        $result = db()->query($sql);

        if ($result == false) return null;
        return $result[0];
    }
}
